﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessLibrary.BLL;
using System.Data.SqlClient; 

namespace BusinessLibrary.DAL
{
           class sqlGroupe
        {
            private static Groupe ReaderToObject(SqlDataReader dr)
            {
                Groupe _groupe = new Groupe();
                _groupe.Id = int.Parse(dr["Id"].ToString());
                _groupe.Nom = dr["Nom"].ToString();



                if (!object.ReferenceEquals(dr["Id_classe"], DBNull.Value))
                {
                    _groupe.classe = Classe.GetClasseById(int.Parse(dr["Id_classe"].ToString()));
                }
                return _groupe;
            }

            public static Groupe GetGroupeById(int id)
            {
                Groupe groupe = new Groupe ();
                string strSQL = @"select * from Groupe where UserId=@Id";
                try
                {
                    using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                    {
                        cnx.Open();
                        SqlCommand cmd = new SqlCommand(strSQL, cnx);
                        cmd.Parameters.AddWithValue("@Id", id);
                        SqlDataReader dr = cmd.ExecuteReader();

                        while (dr.Read())
                        {
                            groupe = ReaderToObject(dr);
                        }
                        dr.Close();
                        cnx.Close();
                    }
                }
                catch (Exception) { }
                return groupe;
            }

            public static int insertGroupe(Groupe g)
            {
                int n = 0;
                string strSQL = @"insert into Groupe (Nom,Id_classe) values(@nom,@id_classe)";
                try
                {
                    using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                    {
                        cnx.Open();
                        SqlCommand cmd = new SqlCommand(strSQL, cnx);

                        cmd.Parameters.AddWithValue("@nom", g.Nom);
                        cmd.Parameters.AddWithValue("@id_classe", g.classe.Id);

                        n = cmd.ExecuteNonQuery();
                        cnx.Close();
                    }
                }
                catch (Exception) { }
                return n;
            }


            /// <summary>
            /// 
            /// </summary>
            /// <param name="s"></param>
            /// <returns></returns>
            public static int updateGroupe(Groupe g)
            {
                int n = 0;

                string strSQl = @"UPDATE Groupe SET (@nom IS NULL OR Nom=@nom), Id_classe=@id_classe where Id=@id ";
                try
                { 
                    using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                    {
                        cnx.Open();

                        SqlCommand cmd = new SqlCommand(strSQl, cnx);

                        cmd.Parameters.AddWithValue("@nom", g.Nom);
                        cmd.Parameters.AddWithValue("@id_classe", g.classe.Id);
                        n = cmd.ExecuteNonQuery();

                        cnx.Close();
                    }
                }
                catch (Exception) { }
                return n;
            }



            /// <summary>
            /// 
            /// </summary>
            /// <param name="id"></param>
            /// <param name="idAuthentif"></param>
            /// <returns></returns>
            public static int deletGroupe(int id)
            {
                int n = 0;
                string strSQL = @"delete from Groupe where id=@id)";

                try
                {
                    using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                    {
                        cnx.Open();
                        SqlCommand cmd = new SqlCommand(strSQL, cnx);

                        cmd.Parameters.AddWithValue("@id", id);


                        n = cmd.ExecuteNonQuery();
                        cnx.Close();
                    }
                }
                catch (Exception) { }
                return n;
            }
        }
    }

